Download Implementing a SQL Data Warehouse.70-767.CertKey.2018-10-01.42q.vcex

Vendor: Microsoft
Exam Code: 70-767
Exam Name: Implementing a SQL Data Warehouse
Date: Oct 01, 2018
File Size: 2 MB
Download Exam

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase
Coupon: EXAMFILESCOM

Discount: 20%

ProfExam Simulator ProfExam Simulator ProfExam Simulator ProfExam Simulator ProfExam Simulator
ProfExam Discount

Demo Questions

Question 1
You manage a data warehouse in a Microsoft SQL Server instance. Company employee information is imported from the human resources system to a table named Employee in the data warehouse instance. The Employee table was created by running the query shown in the Employee Schema exhibit. (Click the Exhibit button.) 
  
The personal identification number is stored in a column named EmployeeSSN. All values in the EmployeeSSN column must be unique. 
When importing employee data, you receive the error message shown in the SQL Error exhibit. (Click the Exhibit button.). 
  
You determine that the Transact-SQL statement shown in the Data Load exhibit is the cause of the error. (Click the Exhibit button.) 
  
You remove the constraint on the EmployeeSSN column. You need to ensure that values in the EmployeeSSN column are unique. 
For each of the following statements, select Yes if the statement is true. Otherwise, select No. 
NOTE: Each correct selection is worth one point.
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
With the ANSI standards SQL:92, SQL:1999 and SQL:2003, an UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not. From SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs. References: https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls
With the ANSI standards SQL:92, SQL:1999 and SQL:2003, an UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.
In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not. 
From SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs. 
References: https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls
Question 2
You have a data warehouse. 
You need to move a table named Fact.ErrorLog to a new filegroup named LowCost. 
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
Step 1: Add a filegroup named LowCost to the database.First create a new filegroup. Step 2:The next stage is to go to the ‘Files’ page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files) Step 3:To move a table to a different filegroup involves moving the table’s clustered index to the new filegroup. While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID ON HumanResources.Department(DepartmentID) WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY This recreates the same index but on the SECONDARY filegroup. References: http://www.sqlmatters.com/Articles/Moving%20a%20Table%20to%20a%20Different%20Filegroup.aspx
Step 1: Add a filegroup named LowCost to the database.
First create a new filegroup. 
Step 2:
The next stage is to go to the ‘Files’ page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files) 
Step 3:
To move a table to a different filegroup involves moving the table’s clustered index to the new filegroup. While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :
CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID 
ON HumanResources.Department(DepartmentID) 
WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY 
This recreates the same index but on the SECONDARY filegroup. 
References: http://www.sqlmatters.com/Articles/Moving%20a%20Table%20to%20a%20Different%20Filegroup.aspx
Question 3
Your company has a Microsoft SQL Server data warehouse instance. The human resources department assigns all employees a unique identifier. You plan to store this identifier in a new table named Employee. 
You create a new dimension to store information about employees by running the following Transact-SQL statement:
  
You have not added data to the dimension yet. You need to modify the dimension to implement a new column named [EmployeeKey]. The new column must use unique values. 
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Correct answer: To work with this question, an Exam Simulator is required.
Question 4
You deploy a Microsoft Azure SQL Data Warehouse instance. The instance must be available eight hours each day. 
You need to pause Azure resources when they are not in use to reduce costs. 
What will be the impact of pausing resources? To answer, select the appropriate options in the answer area. 
NOTE: Each correct selection is worth one point. 
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
To save costs, you can pause and resume compute resources on-demand. For example, if you won't be using the database during the night and on weekends, you can pause it during those times, and resume it during the day. You won't be charged for DWUs while the database is paused. When you pause a database:Compute and memory resources are returned to the pool of available resources in the data center Data Warehouse Unit (DWU) costs are zero for the duration of the pause. Data storage is not affected and your data stays intact. SQL Data Warehouse cancels all running or queued operations. When you resume a database:SQL Data Warehouse acquires compute and memory resources for your DWU setting. Compute charges for your DWUs resume. Your data will be available. You will need to restart your workload queries. References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-rest-api
To save costs, you can pause and resume compute resources on-demand. For example, if you won't be using the database during the night and on weekends, you can pause it during those times, and resume it during the day. You won't be charged for DWUs while the database is paused. 
When you pause a database:
Compute and memory resources are returned to the pool of available resources in the data center 
Data Warehouse Unit (DWU) costs are zero for the duration of the pause. 
Data storage is not affected and your data stays intact. 
SQL Data Warehouse cancels all running or queued operations. 
When you resume a database:
SQL Data Warehouse acquires compute and memory resources for your DWU setting. 
Compute charges for your DWUs resume. 
Your data will be available. 
You will need to restart your workload queries. 
References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-rest-api
Question 5
You have a Microsoft SQL Server Data Warehouse instance that uses SQL Server Analysis Services (SSAS). The instance has a cube containing data from an on-premises SQL Server instance. A measure named Measure1 is configured to calculate the average of a column. 
You plan to change Measure1 to a full additive measure and create a new measure named Measure2 that evaluates data based on the first populated row. 
You need to configure the measures. 
What should you do? To answer, select the appropriate options in the answer area. 
NOTE: Each correct selection is worth one point.
Correct answer: To work with this question, an Exam Simulator is required.
Explanation:
Box 1:The default setting is SUM (fully additive). Box 2:FirstNonEmpty: The member value is evaluated as the value of its first child along the time dimension that contains data.References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/define-semiadditive-behavior
Box 1:
The default setting is SUM (fully additive). 
Box 2:
FirstNonEmpty: The member value is evaluated as the value of its first child along the time dimension that contains data.
References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/define-semiadditive-behavior
Question 6
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has three databases as described in the following table. 
  
  
You plan to load at least one million rows of data each night from DB1 into the OnlineOrder table. You must load data into the correct partitions using a parallel process. 
You create 24 Data Flow tasks. You must place the tasks into a component to allow parallel load. After all of the load processes compete, the process must proceed to the next task. 
You need to load the data for the OnlineOrder table. 
What should you use?
  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container
Correct answer: H
Explanation:
The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently. References: http://www.cozyroc.com/ssis/parallel-loop-task
The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently. 
References: http://www.cozyroc.com/ssis/parallel-loop-task
Question 7
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table. 
  
  
Each day, you publish a Microsoft Excel workbook that contains a list of product names and current prices to an external website. Suppliers update pricing information in the workbook. Each supplier saves the workbook with a unique name. 
Each night, the Products table is deleted and refreshed from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. All files must be loaded in sequence. 
You need to add a data flow in an SSIS package to perform the Excel files import in the data warehouse. 
What should you use?
  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container
Correct answer: A
Explanation:
If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.     References: https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-data-from-excel-to-sql
If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow. 
  
  
References: https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-data-from-excel-to-sql
Question 8
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table. 
  
  
Each week, you import a product catalog from a partner company to a staging table in DB2. 
You need to create a stored procedure that will update the staging table by inserting new products and deleting discontinued products. 
What should you use?
  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container
Correct answer: G
Question 9
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table. 
  
  
Each day, data from the table OnlineOrder in DB2 must be exported by partition. The tables must not be locked during the process. 
You need to write a Microsoft SQL Server Integration Services (SSIS) package that performs the data export. 
What should you use?
  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container
Correct answer: E
Explanation:
The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output. References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation
The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output. 
References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation
Question 10
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table. 
  
  
Product prices are updated and are stored in a table named Products on DB1. The Products table is deleted and refreshed each night from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. None of the data sources are sorted. 
You need to update the SSIS package to add current prices to the Products table. 
What should you use?
  1. Lookup transformation
  2. Merge transformation
  3. Merge Join transformation
  4. MERGE statement
  5. Union All transformation
  6. Balanced Data Distributor transformation
  7. Sequential container
  8. Foreach Loop container
Correct answer: D
Explanation:
In the current release of SQL Server Integration Services, the SQL statement in an Execute SQL task can contain a MERGE statement. This MERGE statement enables you to accomplish multiple INSERT, UPDATE, and DELETE operations in a single statement. References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/merge-in-integration-services-packages
In the current release of SQL Server Integration Services, the SQL statement in an Execute SQL task can contain a MERGE statement. This MERGE statement enables you to accomplish multiple INSERT, UPDATE, and DELETE operations in a single statement. 
References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/merge-in-integration-services-packages
CONNECT US
HOW TO OPEN VCE FILES

Use VCE Exam Simulator to open VCE files
Avanaset

HOW TO OPEN VCEX AND EXAM FILES

Use ProfExam Simulator to open VCEX and EXAM files
ProfExam Screen

ProfExam
ProfExam at a 20% markdown

You have the opportunity to purchase ProfExam at a 20% reduced price

Get Now!